﻿Imports System.Text 



Public MustInherit Class DataAccessBase

    Public Enum ConnectionTypeEnum
        SQLCompact = 0
        SQLServer = 1
        OLEDB = 2
        Oracle = 3
    End Enum
    Private Const CNN_STRING_ENTRY As String = "cnnString"
    Public ReadOnly Property ConnectionType() As ConnectionTypeEnum
        Get
            Dim selectedConn As ConnectionTypeEnum

            Dim currentCnnStringEntry = Me.ConnectionStringEntry

            'busca por el attributo de la conexión actual
            Dim intcnType As String = getNodeAttribute(Of String)(currentCnnStringEntry, "cnnType")

            If intcnType IsNot Nothing Then
                selectedConn = [Enum].Parse(GetType(ConnectionTypeEnum), intcnType)
            ElseIf CfgManager.getNodeText(Of String)("cnnType") = Nothing Then

                Dim cn = CfgManager.getConnectionString(currentCnnStringEntry)

                If cn IsNot Nothing Then
                    If cn.ProviderName.Equals("System.Data.SqlClient", StringComparison.InvariantCultureIgnoreCase) Then
                        Return ConnectionTypeEnum.SQLServer
                    End If
                    If cn.ProviderName.ToLower().Contains("SqlCe".ToLower()) Then
                        Return ConnectionTypeEnum.SQLCompact
                    End If
                    If cn.ProviderName.ToLower().Contains("oracle".ToLower()) Then
                        Return ConnectionTypeEnum.Oracle
                    End If

                End If

            Else
                selectedConn = [Enum].Parse(GetType(ConnectionTypeEnum), My.Settings.cnnType)
            End If

            Return selectedConn
        End Get
    End Property

    Public Sub New()

    End Sub

    Public Sub New(ByVal cnnStringEntry As String)
        Me.ConnectionStringEntry = cnnStringEntry
    End Sub

    Private _ConnectionStringEntry As String = CNN_STRING_ENTRY
    Public Property ConnectionStringEntry() As String
        Get
            Return _ConnectionStringEntry
        End Get
        Set(ByVal value As String)
            _ConnectionStringEntry = value
        End Set
    End Property

    Private _getConnectionString As String
    Protected Friend Property getConnectionString() As String
        Get

            If _getConnectionString Is Nothing Then

                If CfgManager.getNodeText(Of String)(ConnectionStringEntry, False) = Nothing Then
                    _getConnectionString = My.Settings.cnnString
                Else
                    If CfgManager.getNodeText(Of String)(ConnectionStringEntry, False) = Nothing Then
                        ConnectionStringEntry = CNN_STRING_ENTRY
                    End If
                    _getConnectionString = CfgManager.getNodeText(Of String)(ConnectionStringEntry)
                End If

            End If

            Return _getConnectionString
        End Get
        Set(ByVal value As String)
            _getConnectionString = value
        End Set
    End Property



    Private _cnn As IDbConnection
    Protected Friend ReadOnly Property CurrentConnection() As IDbConnection
        Get
            If _cnn Is Nothing Then
                Select Case ConnectionType
                    Case ConnectionTypeEnum.OLEDB
                        _cnn = New OleDb.OleDbConnection(getConnectionString)
                    Case ConnectionTypeEnum.SQLCompact
                        _cnn = New SqlServerCe.SqlCeConnection(getConnectionString)
                    Case ConnectionTypeEnum.SQLServer
                        _cnn = New SqlClient.SqlConnection(getConnectionString)
                    Case ConnectionTypeEnum.Oracle
                        _cnn = New OracleClient.OracleConnection(getConnectionString)
                End Select

            End If
            Return _cnn
        End Get
    End Property


    Private _openCounter As Integer = 0
    Protected Friend Sub Open()
        _openCounter += 1
        If CurrentConnection.State <> ConnectionState.Open Then
            CurrentConnection.Open()
        End If
    End Sub

    Protected Friend Sub Close()
        _openCounter += -1
        If _openCounter = 0 AndAlso CurrentConnection.State <> ConnectionState.Closed Then
            CurrentConnection.Close()
        End If
    End Sub

    Protected Friend Function getDataAdapter(ByVal cmd As IDbCommand) As IDbDataAdapter
        Select Case ConnectionType
            Case ConnectionTypeEnum.OLEDB
                Return New OleDb.OleDbDataAdapter(cmd)
            Case ConnectionTypeEnum.SQLCompact
                Return New SqlServerCe.SqlCeDataAdapter(cmd)
            Case ConnectionTypeEnum.SQLServer
                Return New SqlClient.SqlDataAdapter(cmd)
            Case ConnectionTypeEnum.Oracle
                Return New OracleClient.OracleDataAdapter(cmd)
            Case Else
                Throw New NotImplementedException()
        End Select
    End Function

    Protected Friend Function getCommand() As IDbCommand
        Dim c As IDbCommand
        Select Case ConnectionType
            Case ConnectionTypeEnum.OLEDB
                c = New OleDb.OleDbCommand
            Case ConnectionTypeEnum.SQLCompact
                c = New SqlServerCe.SqlCeCommand
            Case ConnectionTypeEnum.SQLServer
                c = New SqlClient.SqlCommand
            Case ConnectionTypeEnum.Oracle
                c = New OracleClient.OracleCommand

            Case Else
                Throw New NotImplementedException()
        End Select
        c.Connection = Me.CurrentConnection
        Return c
    End Function

    Public Function getParam() As IDataParameter
        Select Case ConnectionType
            Case ConnectionTypeEnum.OLEDB
                Return New OleDb.OleDbParameter
            Case ConnectionTypeEnum.SQLCompact
                Return New SqlServerCe.SqlCeParameter
            Case ConnectionTypeEnum.SQLServer
                Return New SqlClient.SqlParameter
            Case ConnectionTypeEnum.Oracle
                Return New OracleClient.OracleParameter

            Case Else
                Throw New NotImplementedException()
        End Select
    End Function

    Public Function getParam(ByVal ParamName As String, Optional ByVal pmtType As DbType = DbType.String) As IDataParameter
        Dim p = getParam()
        ParamName = ParamName.Trim()
        If Not ParamName.StartsWith(getParameterPrefix()) Then
            ParamName = getParameterPrefix() & ParamName
        End If
        p.ParameterName = ParamName
        p.DbType = pmtType
        Return p
    End Function

    Protected Friend Function getParam(ByVal ParamName As String, ByVal ParamValue As Object, Optional ByVal pmtType As DbType = DbType.String) As IDataParameter
        Dim p = getParam(ParamName, pmtType)
        p.Value = ParamValue
        If Me.ConnectionType = ConnectionTypeEnum.Oracle AndAlso ParamValue IsNot DBNull.Value AndAlso ParamValue IsNot Nothing Then
            DirectCast(p, OracleParameter).Size = CStr(ParamValue).Length
        End If
        If p.DbType = DbType.Date OrElse p.DbType = DbType.DateTime OrElse p.DbType = DbType.DateTime2 Then
            If p.Value = DateTime.MinValue OrElse p.Value = DateTime.MaxValue Then
                p.Value = DBNull.Value                 
            End If
        End If

        Return p
    End Function

    Protected Friend Function getParam(Of TClass)(ByVal ParamName As String, Optional ByVal ParamValue As Object = Nothing) As IDataParameter
        Return getParam(ParamName, ParamValue, getDbType(Of TClass)())
    End Function

    Protected Function getParameterPrefix() As String
        Select Case Me.ConnectionType
            Case ConnectionTypeEnum.SQLServer
                Return "@"
            Case ConnectionTypeEnum.Oracle
                Return ":"
            Case Else
                Return "@"
        End Select
    End Function

    Protected Friend Function getParamFromObject(ByVal oParm As Object) As List(Of IDataParameter)

        Dim l As New List(Of IDataParameter)
        For Each item In oParm.GetType().GetProperties().Where(Function(x) x.CanRead AndAlso x.CanWrite)
            Dim paramName As String
            Select Case Me.ConnectionType
                Case ConnectionTypeEnum.SQLCompact, ConnectionTypeEnum.SQLServer
                    paramName = getParameterPrefix() + item.Name
                Case Else
                    paramName = item.Name

            End Select
            Dim paramValue = item.GetValue(oParm, Nothing)
            If paramValue Is Nothing Then
                paramValue = DBNull.Value
            End If
            l.Add(getParam(paramName, paramValue, getDbType(item.PropertyType)))
        Next
        Return l
    End Function

    Protected Friend Function getParamFromObjectPropertyMapped(ByVal oParm As Object) As Dictionary(Of String, IDataParameter)

        Dim l As New Dictionary(Of String, IDataParameter)

        'Dim l As New List(Of IDataParameter)
        For Each item In oParm.GetType().GetProperties().Where(Function(x) x.CanRead)
            Dim paramName As String
            Select Case Me.ConnectionType
                Case ConnectionTypeEnum.SQLCompact, ConnectionTypeEnum.SQLServer
                    paramName = getParameterPrefix() + item.Name
                Case Else
                    paramName = item.Name

            End Select
            Dim paramValue = item.GetValue(oParm, Nothing)
            l.Add(item.Name, getParam(paramName, paramValue, getDbType(item.PropertyType)))
        Next
        Return l
    End Function

    Protected Friend Function getParamFromObject(ByVal oParm As Object, ByVal ParamArray ExcludeList() As String) As List(Of IDataParameter)
        'Dim pList = New List(Of IDataParameter)
        Dim pList As New List(Of IDataParameter)
        For Each item In oParm.GetType().GetProperties().Where(Function(x) x.CanRead AndAlso x.CanWrite AndAlso Not ExcludeList.Contains(x.Name))
            Dim paramName As String
            Select Case Me.ConnectionType
                Case ConnectionTypeEnum.SQLCompact, ConnectionTypeEnum.SQLServer
                    paramName = getParameterPrefix() + item.Name
                Case Else
                    paramName = item.Name

            End Select
            Dim paramValue = item.GetValue(oParm, Nothing)
            If paramValue Is Nothing Then
                paramValue = DBNull.Value
            End If
            pList.Add(getParam(paramName, paramValue, getDbType(item.PropertyType)))
        Next

        Dim lNuewva As New List(Of IDataParameter)
        For Each p In pList
            Dim pn = p
            'Dim agrega As Boolean = True
            'For Each item In ExcludeList
            '    If item.Equals(pn, StringComparison.InvariantCultureIgnoreCase) Then
            '        agrega = False
            '        Exit For
            '    End If
            'Next

            'TODO CAMBIARLO CON EL SELECT DE LA LIST
            Dim parmName As String
            If pn.ParameterName.StartsWith(getParameterPrefix()) Then
                parmName = pn.ParameterName.Remove(0, getParameterPrefix().Length)
            Else
                parmName = pn.ParameterName
            End If


            If Not ExcludeList.Where(Function(X) X.Equals(parmName, StringComparison.InvariantCultureIgnoreCase)).Any() Then
                lNuewva.Add(p)
            End If
        Next
        Return lNuewva
    End Function


    Protected Friend Function getDbType(ByVal theType1 As Type) As DbType

        Dim theType = gettypeFromNullable(theType1)

        Select Case theType.Name.ToLower()
            Case "uinteger", "uint32"
                theType = GetType(Int32)
            Case "uint16", "ushort"
                theType = GetType(Int16)
            Case "ulong", "uint64"
                theType = GetType(Int64)
            Case Else
                If theType.Name.ToLower().StartsWith("u") Then
                    Throw New ApplicationException("TIpo no implementado: " & theType.Name & " DataAccesBase.getDbType")
                End If
        End Select

        Dim p1 As IDataParameter = Me.getParam()
        Dim tc As System.ComponentModel.TypeConverter

        'p1 = New SqlClient.SqlParameter()
        tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType)

        If tc.CanConvertFrom(theType) Then
            p1.DbType = tc.ConvertFrom(theType.Name)
        Else
            'Try brute force
            Try

                p1.DbType = tc.ConvertFrom(theType.Name)


            Catch ex As Exception
                'Do Nothing
            End Try
        End If
        Return p1.DbType
    End Function
    Protected Friend Function getDbType(Of TClass)() As DbType
        Dim theType = GetType(TClass)

        Return getDbType(theType)
    End Function




    Protected Friend Sub ExecuteSQLNonQuery(ByVal cmd As IDbCommand)
        Try
            cmd.Connection = CurrentConnection
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Protected Friend Sub ExecuteStoredProcedureNonQuery(ByVal spName As String)
        Try
            Using cmd As IDbCommand = getCommand()
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = spName
                cmd.Connection = CurrentConnection
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
            End Using

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Protected Friend Sub ExecuteStoredProcedureNonQuery(ByVal spName As String, ByVal ParamArray pmts() As IDataParameter)
        Try
            Using cmd As IDbCommand = getCommand()
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = spName
                cmd.Connection = CurrentConnection
                cmd.Parameters.Clear()
                ParametizeCommand(cmd, pmts)

                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
            End Using

        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Protected Friend Sub ExecuteTextNonQuery(ByVal strSql As String, ByVal pmts As List(Of IDataParameter))
        Try
            ExecuteTextNonQuery(strSql, pmts.ToArray())
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Protected Friend Sub ExecuteTextNonQuery(ByVal strSql As String, ByVal ParamArray pmts() As IDataParameter)
        Try
            Using cmd As IDbCommand = getCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = strSql
                cmd.Connection = CurrentConnection
                cmd.Parameters.Clear()
                ParametizeCommand(cmd, pmts)

                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
            End Using


        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Protected Friend Function ExecuteSQLGetDataSet(ByVal cmd As IDbCommand) As DataSet
        Try

            cmd.Connection = CurrentConnection
            Dim da As IDbDataAdapter = getDataAdapter(cmd)
            Dim ds As New DataSet
            da.Fill(ds)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Protected Friend Function ExecuteSQLGetDataSet(ByVal strSql As String) As DataSet
        Dim ds As New DataSet
        Using cmd As IDbCommand = getCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = strSql
            ds = ExecuteSQLGetDataSet(cmd)
            cmd.Parameters.Clear()
        End Using
        Return ds
    End Function

    Protected Friend Function ExecuteSQLGetDataSet(ByVal strSql As String, ByVal pmt As IDataParameter) As DataSet
        If pmt Is Nothing Then
            Return ExecuteSQLGetDataSet(strSql)
        End If
        Return ExecuteSQLGetDataSet(strSql, New IDataParameter() {pmt})
    End Function

    Protected Friend Function ExecuteSQLGetDataSet(ByVal strSql As String, ByVal ParamArray pmts() As IDataParameter) As DataSet
        Dim ds As New DataSet
        Using cmd As IDbCommand = getCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = strSql
            cmd.Parameters.Clear()
            ParametizeCommand(cmd, pmts)
            ds = ExecuteSQLGetDataSet(cmd)
            cmd.Parameters.Clear()
        End Using
        Return ds
    End Function


    Protected Friend Function ExecuteSQLGetList(Of MiClase)(ByVal cmd As IDbCommand) As IEnumerable(Of MiClase)
        Try
            Dim fill As New EntityFiller(Of MiClase)()
            Dim lista As IList(Of MiClase)

            Using reader As IDataReader = cmd.ExecuteReader()
                lista = fill.Fill(reader)
            End Using

            Return lista

            'Dim ds As DataSet = ExecuteSQLGetDataSet(cmd)
            'Dim listadoCollection As New List(Of MiClase)
            'For Each dataRowItem As DataRow In ds.Tables(0).Rows
            '    Dim entityInstance = getInstance(Of MiClase)()
            '    entityInstance = MapEntityFromDR(Of MiClase)(dataRowItem)
            '    listadoCollection.Add(entityInstance)
            'Next
            'Return listadoCollection
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Protected Friend Function ExecuteSQLGetList(Of MiClase)(ByVal strSql As String) As IEnumerable(Of MiClase)
        Dim l As IEnumerable(Of MiClase)
        Using cmd As IDbCommand = getCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = strSql
            l = ExecuteSQLGetList(Of MiClase)(cmd)
            cmd.Parameters.Clear()
        End Using
        Return l
    End Function


    Private Sub ParametizeCommand(ByRef cmd As IDbCommand, ByVal ParamArray pmts() As IDataParameter)
        cmd.Parameters.Clear()
        If pmts IsNot Nothing Then
            For Each item In pmts.Where(Function(x) x IsNot Nothing)
                If item.Value Is Nothing Then
                    item.Value = DBNull.Value
                End If
                cmd.Parameters.Add(item)

            Next
        End If
    End Sub

    Protected Friend Function ExecuteSQLGetList(Of MiClase)(ByVal strSql As String, ByVal ParamArray pmts() As IDataParameter) As IEnumerable(Of MiClase)
        Dim l As IEnumerable(Of MiClase)
        Using cmd As IDbCommand = getCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = strSql
            cmd.Parameters.Clear()
            ParametizeCommand(cmd, pmts)

            l = ExecuteSQLGetList(Of MiClase)(cmd)
            cmd.Parameters.Clear()
        End Using
        Return l
    End Function

    Protected Friend Function ExecuteSQLGetList(Of MiClase)(ByVal strSql As String, ByVal params As List(Of IDataParameter)) As IEnumerable(Of MiClase)
        Return ExecuteSQLGetList(Of MiClase)(strSql, params.ToArray())
    End Function

    Protected Friend Function getSelectQuery(ByVal Table_name As String, ByVal WhereStatement As String) As String
        WhereStatement = WhereStatement.Trim()
        If Not String.IsNullOrEmpty(WhereStatement) AndAlso Not WhereStatement.StartsWith("where", StringComparison.InvariantCultureIgnoreCase) Then
            WhereStatement = " Where " & WhereStatement
        End If
        Return String.Format("Select * From {0} {1}", Table_name, WhereStatement)

    End Function

    Protected Friend Function getRecordCountStatement(ByVal strSql As String) As String
        Select Case Me.ConnectionType
            Case ConnectionTypeEnum.Oracle
                Return String.Format("Select NVL(count(*),0) from ({0})", strSql)
            Case ConnectionTypeEnum.SQLServer
                Return String.Format("Select ISNULL(count(*),0) from ({0})", strSql)
            Case Else
                Return String.Format("Select count(*) from ({0})", strSql)
        End Select

    End Function

    Public Function getSelectQuery(ByVal TableName As String, _
                                               ByVal WhereStatement As String, _
                                               ByVal OrderByField As String, _
                                               ByVal rowFrom As Integer, _
                                               ByVal rowTo As Integer) As String()
        Dim strSQL As String
        WhereStatement = WhereStatement.Trim()
        If Not String.IsNullOrEmpty(WhereStatement) AndAlso Not WhereStatement.StartsWith("where", StringComparison.InvariantCultureIgnoreCase) Then
            WhereStatement = " Where " & WhereStatement
        End If
        Dim sb As New StringBuilder()
        Dim sqlOnly As String
        TableName = TableName.Trim()

        Select Case Me.ConnectionType

            Case ConnectionTypeEnum.Oracle

                sb.AppendFormat("Select *  From (Select rownum rnum, a.* From ")
                sb.AppendFormat("( ")

                If TableName.StartsWith("(") Then

                    sb.AppendFormat("Select  tbl.* From {0} tbl {1} Order By {2} ", TableName, WhereStatement, OrderByField)

                Else
                    sb.AppendFormat("Select  {0}.* From {0} {1} Order By {2} ", TableName, WhereStatement, OrderByField)
                End If


                sb.AppendFormat(") a where rownum <= {0} ) ", rowTo)
                sb.AppendFormat("where rnum >= {0} ", rowFrom)
                strSQL = sb.ToString()

                If TableName.StartsWith("(") Then
                    sqlOnly = String.Format("Select  tbl1.* From {0} tbl1 {1}", TableName, WhereStatement)
                Else
                    sqlOnly = String.Format("Select  {0}.* From {0} {1}", TableName, WhereStatement)
                End If

            Case Else

                sb.AppendFormat(" SELECT  * ")
                sb.AppendFormat(" FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY {0} ) AS RowNum, * ", OrderByField)
                sb.AppendFormat("           FROM {0} ", TableName)
                sb.AppendFormat("           {0} ", WhereStatement)
                sb.AppendFormat("         ) AS RowConstrainedResult ")
                sb.AppendFormat(" WHERE   RowNum >= {0} ", rowFrom)
                sb.AppendFormat("     AND RowNum =< {0} ", rowTo)
                sb.AppendFormat(" ORDER BY RowNum ")
                strSQL = sb.ToString()
                sqlOnly = String.Format("Select  {0}.* From {0} {1}", TableName, WhereStatement)
        End Select

        Return New String() {strSQL, getRecordCountStatement(sqlOnly)}

    End Function




    Protected Friend Function getUpdateQuery(ByVal Table_name As String, ByVal values As String, ByVal WhereStatement As String) As String

        Return String.Format("Update {0} set {1} {2}", Table_name, values, WhereStatement)

    End Function


End Class
